package cn.edu.zjicm.wordsnet_d.db;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import android.util.Log;
import cn.edu.zjicm.wordsnet_d.R;
import cn.edu.zjicm.wordsnet_d.data.Location;
import cn.edu.zjicm.wordsnet_d.data.School;
import cn.edu.zjicm.wordsnet_d.util.LogUtil;

public class LocationAndSchoolFactory {
	public static final String TABLE_LOCATION = "youqu_location";

	private static SQLiteDatabase db;
	public static LocationAndSchoolFactory instance = null;
	private Context context;

	private void openDB(Context context) {
		if (db == null || !db.isOpen()) {
			try {
				db = new DatabaseHelperLocationAndSchool(context).getWritableDatabase();
				LogUtil.addLog("dbversion:" + db.getVersion());
			} catch (Throwable t) {
				Log.e("error", "exit");
				LogUtil.addLog("opendb fail");
				System.exit(0);
			}
		}
	}

	private LocationAndSchoolFactory(Context context) {
		openDB(context);
		if (instance == null) {
			instance = this;
		}
		this.context = context;
	}

	public synchronized static LocationAndSchoolFactory getInstance(Context context) {
		if (instance == null) {
			instance = new LocationAndSchoolFactory(context);

			// 如果表格youqu_location不存在(新安装用户)或参数为第一次使用新版(数据库升级用户)，则保存用户信息，复制数据库，回复用户数据
			if (!instance.tabbleIsExist(LocationAndSchoolFactory.TABLE_LOCATION)) {
				db.close();
				instance = null;

				// Log.e("db version",WordFactory.getInstance(this).getVersion()+"");
				copyDB(context);
				// Log.e("db version",WordFactory.getInstance(this).getVersion()+"");

				instance = new LocationAndSchoolFactory(context);
			}
		}
		return instance;
	}

	public static void copyDB(Context context) {
		File file = new File(DatabaseHelperLocationAndSchool.DB_PATH + DatabaseHelperLocationAndSchool.DATABASE_NAME);
		try {
			FileOutputStream os = new FileOutputStream(file);
			InputStream in = context.getResources().openRawResource(R.raw.db_location_school);
			byte[] buffer = new byte[4096];
			while (in.available() > 0) {
				int i = in.read(buffer);
				if (i > 0)
					os.write(buffer, 0, i);
				os.flush();
			}
			in.close();
			os.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public boolean tabbleIsExist(String tableName) {
		boolean result = false;
		if (tableName == null) {
			return false;
		}

		String sql = "select count(1) as c from Sqlite_master where type ='table' and name ='" + tableName.trim() + "' ";
		int count = getInt(sql);
		if (count > 0) {
			result = true;
		} else {
			// db.close();
			// instance = null;
		}
		return result;
	}

	public int getInt(String sql) {
		Cursor c = db.rawQuery(sql, null);
		int ret = 0;
		if (c.moveToNext()) {
			ret = c.getInt(0);
		}
		c.close();
		return ret;
	}

	// 获取数据库版本号
	public int getVersion() {
		instance = null;
		return db.getVersion();
	}

	/* location */
	// 获取location列表
	public List<Location> getLocationsList(String sql) {
		Cursor c;
		List<Location> locations = new ArrayList<Location>();

		c = db.rawQuery(sql, null);
		while (c.moveToNext()) {
			Location loc = new Location(c);
			locations.add(loc);
		}
		c.close();
		return locations;
	}

	public List<School> getSchoolList(String sql) {
		Cursor c;
		List<School> schools = new ArrayList<School>();

		c = db.rawQuery(sql, null);
		while (c.moveToNext()) {
			School school = new School(c);
			schools.add(school);
		}
		c.close();
		return schools;
	}

	// 获取省份信息
	public List<Location> getProvincesList() {
		String sql = "select distinct province_id,province_name from youqu_location order by province_id";
		return getLocationsList(sql);
	}

	// 获取城市信息
	public List<Location> getCitysList(int provinceId) {
		String sql = "select distinct city_id,city_name from youqu_location where province_id=" + provinceId + " order by city_id";
		return getLocationsList(sql);
	}

	// 获取区县信息
	public List<Location> getRegionsList(int cityId) {
		String sql = "select distinct region_id,region_name from youqu_location where city_id=" + cityId + " order by region_id";
		return getLocationsList(sql);
	}

	// 获取直辖市的区县信息
	public List<Location> getRegionsListOfMunicipality(int provinceId) {
		String sql = "select distinct region_id,region_name from youqu_location where province_id=" + provinceId + " order by region_id";
		return getLocationsList(sql);
	}

	// 查询高校信息
	public List<School> getUniversity(int provinceId) {
		String sql = "select * from youqu_school where province_id=" + provinceId + " and school_level=1 order by id";
		return getSchoolList(sql);
	}

	// 查询高校信息
	public List<School> getUniversity(int provinceId, String text) {
		text = text.replace("'", "''");
		String sql = "select * from youqu_school where province_id=" + provinceId + " and school_level=1 " + "and school_name like '" + text
				+ "%' order by id";
		return getSchoolList(sql);
	}

	// 查询高中(school_level为2)、中专技校(school_level为3)、初中(school_level为4)信息
	public List<School> getHighSchool(int regionId, int schoolLevel) {
		String sql = "select * from youqu_school where region_id=" + regionId + " and school_level=" + schoolLevel + " order by id";
		return getSchoolList(sql);
	}

	// 查询高中(school_level为2)、中专技校(school_level为3)、初中(school_level为4)信息
	public List<School> getHighSchool(int regionId, int schoolLevel, String text) {
		text = text.replace("'", "''");
		String sql = "select * from youqu_school where region_id=" + regionId + " and school_level=" + schoolLevel + " and school_name like '" + text
				+ "%' order by id";
		return getSchoolList(sql);
	}

	private String getProvinceNameByID(int id) {
		String sql = "select province_name from youqu_location where province_id=" + id;
		String str = "";
		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			str = cursor.getString(0);
		}
		cursor.close();
		return str;
	}

	private String getRegionNameByID(int id) {
		String sql = "select region_name from youqu_location where region_id=" + id;
		String str = "";
		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			str = cursor.getString(0);
		}
		cursor.close();
		return str;
	}

	public String getSchoolNameById(int id) {
		String sql = "select school_name from youqu_school where id=" + id;
		String schoolName = "";
		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			schoolName = cursor.getString(0);
		}
		cursor.close();
		return schoolName;
	}

	// 导入学校
	public void importTableSchoolList(List<School> schoolList) {
		if (schoolList == null)
			return;

		db.beginTransaction();

		try {
			for (int i = 0; i < schoolList.size(); i++) {
				School wl = schoolList.get(i);
				String sql = "replace into youqu_school values ";
				sql = sql + "(" + wl.getId() + ",'" + wl.getSchoolName() + "'," + wl.getStudentCount() + "," + wl.getSchoolLevel() + ","
						+ wl.getProvinceId() + "," + wl.getCityId() + "," + wl.getRegionId() + ",'" + wl.getGmtCreate() + "','" + wl.getGmtModified()
						+ "')";
				db.execSQL(sql);
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {

		} finally {
			db.endTransaction(); // 处理完成
		}

		// LogUtil.addLog("导入words_log完成");
	}
}